package com.xuwang.service.impl;

import com.xuwang.mapper.GovernmentStaffMapper;
import com.xuwang.pojo.GovernmentStaff;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author thinknovo
 * @version 1.0
 */
@Service
public class ImportExcelServiceImpl {

    @Resource
    private GovernmentStaffMapper governmentStaffMapper;

    public Map<String, Object> importExcel(MultipartFile file, HttpServletRequest request, String abPath) throws IOException {
        Map<String, Object> map = new HashMap<>();
        String uploadPath = request.getServletContext().getRealPath("") + File.separator + abPath + File.separator;   // 得到相当项目工程的硬盘物理地址
        String fileName = file.getOriginalFilename();                   // 获取上传的文件名
        File newFile = new File(uploadPath + fileName);         // 根据当前工程的物理路径+文件名生成一个file对象
        System.out.println("newFile=" + newFile);
        FileCopyUtils.copy(file.getBytes(), newFile);                   // 通过上传通道中的字节码完成file文件的写入操作

        String path = newFile.toString();                                  // 获取文件的物理路径
        InputStream is = new FileInputStream(path);;                       // 生成输入流
        HSSFWorkbook workbook = new HSSFWorkbook(is);                      // HSSFWorkbook类型对应2003版本
        List<GovernmentStaff> list = new ArrayList<>();
        GovernmentStaff governmentStaff = null;
        Sheet sheet = workbook.getSheetAt(0);                       //模板数据默认读取第一个工作页sheet，index默认从0开始，如果存在多个sheet，那么需要循环Sheet判断

        for(int i = 3; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            governmentStaff = new GovernmentStaff();
            Cell cell = row.getCell(0);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setStaffName(cell.getStringCellValue());
            cell = row.getCell(1);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setSex("男".equals(cell.getStringCellValue())?0:1);
            //governmentStaff.setSex(Integer.parseInt(cell.getStringCellValue()));
            cell = row.getCell(2);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setIdNumber(cell.getStringCellValue());
             cell = row.getCell(3);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setLawNumber(cell.getStringCellValue());
            cell = row.getCell(4);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setPersonalPhone(cell.getStringCellValue());
            cell = row.getCell(5);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setOfficePhone(cell.getStringCellValue());
            cell = row.getCell(6);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            governmentStaff.setStaffNumber(Integer.parseInt(cell.getStringCellValue()));
            list.add(governmentStaff);
        }
        System.out.println("用户上传的数据："+list);
        governmentStaffMapper.insertAll(list);
        map.put("code", 200);
        map.put("msg", "上传用户数据成功");
        return map;
    }
}
